import java.awt.Color;
import java.awt.Dimension;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.ResultSet;

import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.table.DefaultTableModel;

import com.mysql.jdbc.PreparedStatement;


class EmpPayrollForm extends JPanel{
	private static final long serialVersionUID = 1L;
	private  ImageIcon payH = new ImageIcon(this.getClass().getResource("images/header/payr.png"));
	JLabel payr;
	private static PayrollPane payroll = new PayrollPane();

	public EmpPayrollForm(){

		payr = new JLabel(payH);
		payr.setBounds(135, 35, payH.getIconWidth(), payH.getIconHeight());
		payr.setVisible(true);

		payroll.setBounds(130, 65, 617, 437);

		add(payroll);
		add(payr);

		setLayout(null);
		setOpaque(false);
		setSize(806,628);
		setVisible(true);



	}

	public PayrollPane getPane(){
		return payroll;
	}
}

class PayrollPane extends JPanel{
	private static final long serialVersionUID = 1L;
	private JButton search;
	private JLabel frlabel, tolabel;
	private JComboBox smonth, sday, syear, emonth, eday, eyear;
	private JTable tableMod;
	private DefaultTableModel attendList;
	String[] monthItems = { "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"};
	String[] yearItems = { "2011"};
	int[] intDay = new int[31];
	String[] dayItems = new String[31];
	JLabel	hours, totDed, totBon, baseSal, totSal, totCost;

	public PayrollPane() { 
		
		hours = new JLabel("Working Hours: ");
		baseSal = new JLabel("Base Salary: ");
		totDed = new JLabel("Total Deductions: ");
		totBon = new JLabel("Total Bonuses: ");
		totSal = new JLabel("Total Salary: ");
		totCost = new JLabel("Total Cost: ");

		//construction of table PayrollSummary
		String[] columnNames = {"Date", "Type", "Amount", "Reason"};
		tableMod = new JTable();
		tableMod.setModel(new javax.swing.table.DefaultTableModel(null, columnNames){
			private static final long serialVersionUID = 1L;
			public boolean isCellEditable(int rowIndex, int columnIndex) {
				return false;
			}
		});
		tableMod.setFillsViewportHeight(true);
		tableMod.getTableHeader().setReorderingAllowed(false);
		attendList = (DefaultTableModel)tableMod.getModel();
		JScrollPane	scrollPane = new JScrollPane();
		scrollPane.setViewportView(tableMod);

		search = new JButton ("Search");
		frlabel = new JLabel ("from:");
		tolabel = new JLabel ("to:");
		smonth = new JComboBox(monthItems);
		sday = new JComboBox();
		syear = new JComboBox(yearItems);
		emonth = new JComboBox(monthItems);
		eday = new JComboBox();
		eyear = new JComboBox(yearItems);

		InitializeDrop(new Integer(31), "start");
		InitializeDrop(new Integer(31), "end");

		//adjust size and set layout
		setPreferredSize (new Dimension (617, 437));
		setLayout (null);
		setOpaque(false);
		setSize(617,437);
		setVisible(true);


		add (scrollPane);
		add (search);
		add (frlabel);
		add (tolabel);
		add (smonth);
		add (sday);
		add (syear);
		add (emonth);
		add (eday);
		add (eyear);
		add(totDed);
		add(totBon);
		add(totSal);
		add(totCost);
		add(hours);
		add(baseSal);
		
		search.setForeground(Color.WHITE);
		search.setBackground(Color.BLACK);

		//set component bounds (only needed by Absolute Positioning)
		search.setBounds (285, 25, 100, 25);
		frlabel.setBounds (20, 25, 35, 25);
		smonth.setBounds(50, 25, 90, 25);
		sday.setBounds(140, 25, 50, 25);
		syear.setBounds(190, 25, 80, 25);
		tolabel.setBounds(30, 55, 20, 15);
		emonth.setBounds(50, 55, 90, 25);
		eday.setBounds(140, 55, 50, 25);
		eyear.setBounds(190, 55, 80, 25);
		scrollPane.setBounds(20, 90, 590, 250);
		baseSal.setBounds(30, 340, 250, 30);
		hours.setBounds(30, 370, 250, 30);
		totSal.setBounds(30, 400, 250, 30);
		totDed.setBounds(330, 340, 250, 30);
		totBon.setBounds(330, 370, 250, 30);
		totCost.setBounds(330,400, 250, 30);
		
		//set text of payroll summary label
		baseSal.setText("Base Salary: " + "salary");
		hours.setText("Working Hours: " + "ave");
		totCost.setText("Net Pay: " + "ave");
		totDed.setText("Total Deductions: " + "deduc");
		totBon.setText("Total Bonuses: " + "bonus");
		totSal.setText("Total Salary: " + "sal");
		
		smonth.addActionListener(new ActionListener() {
			public void actionPerformed (ActionEvent e){

				boolean leap = false;

				leap = CheckLeap(Integer.parseInt(syear.getSelectedItem().toString()));

				if(smonth.getSelectedIndex() == 1)
				{
					if(leap==false)
						InitializeDrop(28, "start");
					else
						InitializeDrop(29, "start"); 
				}
				else if (smonth.getSelectedIndex() == 0 || smonth.getSelectedIndex() == 2 || smonth.getSelectedIndex() == 4 
						|| smonth.getSelectedIndex() == 6 || smonth.getSelectedIndex() == 7 || smonth.getSelectedIndex() == 9
						|| smonth.getSelectedIndex() == 11)
					InitializeDrop(31, "start");
				else if (smonth.getSelectedIndex() == 3 || smonth.getSelectedIndex() == 5 || smonth.getSelectedIndex() == 8
						|| smonth.getSelectedIndex() == 10)
					InitializeDrop(30, "start");
			}
		}
		);

		emonth.addActionListener(new ActionListener() {
			public void actionPerformed (ActionEvent e){

				boolean leap = false;

				leap = CheckLeap(Integer.parseInt(eyear.getSelectedItem().toString()));

				if(emonth.getSelectedIndex() == 1)
				{
					if(leap==false)
						InitializeDrop(28, "end");
					else
						InitializeDrop(29, "end"); 
				}
				else if (emonth.getSelectedIndex() == 0 || emonth.getSelectedIndex() == 2 || emonth.getSelectedIndex() == 4 
						|| emonth.getSelectedIndex() == 6 || emonth.getSelectedIndex() == 7 || emonth.getSelectedIndex() == 9
						|| emonth.getSelectedIndex() == 11)
					InitializeDrop(31, "end");
				else if (emonth.getSelectedIndex() == 3 || emonth.getSelectedIndex() == 5 || emonth.getSelectedIndex() == 8
						|| emonth.getSelectedIndex() == 10)
					InitializeDrop(30, "end");
			}
		}
		);
		
		search.addActionListener(new ActionListener(){
			public void actionPerformed(ActionEvent e) {
					payrollUpdate();				
			}
			
		});
	}

	public void InitializeDrop(int ctr, String type) {
		if(type.equals("start")){
			sday.removeAllItems();

			for(int a = 1; a <= ctr; a++ )
			{     
				intDay[a-1] = a;
				dayItems[a-1] = Integer.toString(intDay[a-1]);
				sday.addItem(dayItems[a-1]);
			}
			sday.repaint();
		}
		else if(type.equals("end")){
			eday.removeAllItems();
			for(int a = 1; a <= ctr; a++ )
			{     
				intDay[a-1] = a;
				dayItems[a-1] = Integer.toString(intDay[a-1]);
				eday.addItem(dayItems[a-1]);
			}

			eday.repaint();

		}
	}


	public boolean CheckLeap(int year) {
		if(year%4 == 0)
		{
			if(year%100 != 0)
			{
				return true;
			}
			else
			{
				if(year%400 == 0)
					return true;
				else
					return false;
			}
		}
		return false;
	}



	public void payrollUpdate(){

		try
		{
			ConnectionFactory myFactory = ConnectionFactory.getFactory();
			Connection conn = myFactory.getConnection();
			PreparedStatement stat = (PreparedStatement) conn.prepareStatement("SELECT dateSal as 'Date', s.desc as 'Type', amount, reason"+"" +
					" from bernalcompany.deductionbonus p, bernalcompany.payrollstatus s where p.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"' and p.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"'"+
					" and p.ptype=s.pType and p.idpayroll="+MainPanel.getUID());
			ResultSet res = stat.executeQuery();

			attendList.getDataVector().removeAllElements();
			tableMod.setAutoCreateRowSorter(true);

			while(res.next()){
				attendList.addRow(new Object[]{res.getString("Date"), res.getString("Type"), res.getFloat("amount"), res.getString("reason")});
				attendList.fireTableDataChanged();
			}
			
			conn.close();

			Connection conn2 = myFactory.getConnection();
			PreparedStatement stat2 = (PreparedStatement) conn2.prepareStatement("SELECT	t1.Bonus as 'Bonus', t2.Deduction as Deduction, t3.WorkHours as 'Work Hours', t4.baseSalary as 'Base Salary' from"+
					"(SELECT  ROUND(SUM(amount),2) as 'Bonus' from bernalcompany.deductionbonus p where p.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"' and p.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"'  and p.idpayroll="+MainPanel.getUID()+" and ptype = 0) as t1,"+
					"(SELECT  ROUND(SUM(amount),2) as 'Deduction', p.dateSal as 'Date' from bernalcompany.deductionbonus p where p.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"' and p.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and p.idpayroll="+MainPanel.getUID()+" and ptype = 1) as t2,"+
					"(SELECT  SUM(HOUR(TIMEDIFF(timeOut,timeIn)))+FLOOR(SUM(MINUTE(TIMEDIFF(timeOut,timeIn)))/60+(SUM(SECOND(TIMEDIFF(timeOut,timeIn)))/60)) as 'WorkHours' from bernalcompany.attendance a where a.dateSal >= '"+getSyear()+"-"+getSmonth()+"-"+getSday()+"' and a.dateSal <= '"+getEyear()+"-"+getEmonth()+"-"+getEday()+"' and a.idemployee ="+MainPanel.getUID()+") as t3,"+
					"(SELECT  baseSalary from bernalcompany.employee e where e.idemployee="+MainPanel.getUID()+") as t4");
			ResultSet res2 = stat2.executeQuery();
			
			if(res2.next()){
				hours.setText("Working Hours: "+res2.getInt("Work Hours"));
				baseSal.setText("Base Salary: "+res2.getFloat("Base Salary"));
				totBon.setText("Total Bonus: "+res2.getFloat("Bonus"));
				totDed.setText("Total Deduction: "+res2.getFloat("Deduction"));
				totSal.setText("Total Salary: "+(res2.getInt("Work Hours")*(res2.getFloat("Base Salary")/8)));
				totCost.setText("Total Cost: "+((res2.getInt("Work Hours")*(res2.getFloat("Base Salary")/8))-res2.getFloat("Deduction")+res2.getFloat("Bonus")));
				
			}
			
			conn2.close();
		
		}catch(Exception ex){
			ex.printStackTrace();
		}

	}
	public int getSmonth() {
		String mon = smonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public String getSday() {
		return sday.getSelectedItem().toString();
	}

	public String getSyear() {
		return syear.getSelectedItem().toString();
	}

	public int getEmonth() {
		String mon = emonth.getSelectedItem().toString();
		int con = 0;
		if(mon.equals("January"))
			con = 1;
		else if(mon.equals("February"))
			con = 2;
		else if(mon.equals("March"))
			con = 3;
		else if(mon.equals("April"))
			con = 4;
		else if(mon.equals("May"))
			con = 5;
		else if(mon.equals("June"))
			con = 6;
		else if(mon.equals("July"))
			con = 7;
		else if(mon.equals("August"))
			con = 8;
		else if(mon.equals("September"))
			con = 9;
		else if(mon.equals("October"))
			con = 10;
		else if(mon.equals("November"))
			con = 11;
		else
			con = 12;

		return con;
	}

	public String getEday() {
		return eday.getSelectedItem().toString();
	}

	public String getEyear() {
		return eyear.getSelectedItem().toString();
	}

}
